package apj.demo.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import apj.demo.dao.BillDao;
import apj.demo.model.Bill;
import apj.demo.util.JDBCUtil;
/**
 * 账单实现操作方法类
 * */
public class BillDaoImpl implements BillDao {

	private final String SAVE="insert bill (vendor,bill_pay,bill_date,bill_due_date,bill_finish,pay_type)values(?,?,?,?,?,?)";
	private final String FIND_BY_ALL="select * from bill";
	private final String FIND_BY_ID="select * from bill where bill_id = ?";
	
	
	/**
	 * 保存账单方法
	 * */
	public void save(Bill bill) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(SAVE);
			
			//填充SQL语句参数
			ps.setString(1, bill.getVendor());
			ps.setDouble(2, bill.getBillPay());
			ps.setDate(3, bill.getBillDate());
			ps.setDate(4, bill.getBillDueDate());
			//如果账单处于没完成状态，则设置其值为F
			if(bill.isBillFinish()){
				ps.setString(5, "T");
			}else{
				ps.setString(5, "F");
			}
			ps.setString(6, bill.getPayType());
			
			//执行语句
			ps.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
	}


	/**
	 * 查找全部账单方法
	 * */
	public List<Bill> findByAll() throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Bill> bills = new ArrayList<Bill>();
		Bill bill = null;
		
		try{
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(FIND_BY_ALL);
			rs = ps.executeQuery();
			
			while(rs.next()){
				bill = new Bill();
				bill.setBillId(rs.getInt("bill_id"));
				bill.setVendor(rs.getString("vendor"));
				bill.setBillPay(rs.getDouble("bill_pay"));
				bill.setBillDate(rs.getDate("bill_date"));
				bill.setBillDueDate(rs.getDate("bill_due_date"));
				if(rs.getString("bill_finish").equals("F")){
					bill.setBillFinish(false);
				}else{
					bill.setBillFinish(true);
				}
				bill.setPayType(rs.getString("pay_type"));
				
				//将账单对象，添加到集合中
				bills.add(bill);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(rs);
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
		return bills;
	}


	/**
	 * ID查找账单方法
	 * */
	public Bill findById(int id) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Bill bill = null;
		
		try{
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(FIND_BY_ID);
			//填充参数id
			ps.setInt(1, id);
			rs = ps.executeQuery();
			
			if(rs.next()){
				bill = new Bill();
				bill.setBillId(rs.getInt("bill_id"));
				bill.setVendor(rs.getString("vendor"));
				bill.setBillPay(rs.getDouble("bill_pay"));
				bill.setBillDate(rs.getDate("bill_date"));
				bill.setBillDueDate(rs.getDate("bill_due_date"));
				if(rs.getString("bill_finish").equals("F")){
					bill.setBillFinish(false);
				}else{
					bill.setBillFinish(true);
				}
				bill.setPayType(rs.getString("pay_type"));
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(rs);
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
		return bill;
	}

}
